﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace Fotobanka
{
    /// <summary>
    /// Obsluzna trida pro hodnoceni fotek
    /// </summary>
    public class RatingsManager
    {
        SqlConnection connection;
        SqlCommand command;
        SqlDataReader reader;

        /// <summary>
        /// Vyhleda hodnoceni fotky
        /// </summary>
        /// <param name="photo_id">id fotky</param>
        /// <returns>hodnoceni</returns>
        public int SelectRatingForPhoto(int photo_id)
        {
            int rate = 0;
            using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
            {
                connection.Open();
                command = new SqlCommand("SELECT SUM(rate) / COUNT(photo_id) AS rate FROM Ratings WHERE (photo_id = @id)", connection);
                command.Parameters.Add(new SqlParameter("@id", photo_id));

                reader = command.ExecuteReader();

                if (reader.HasRows && reader.Read())
                {
                    try { rate = (int)reader["rate"]; }
                    catch (Exception ex) { Console.Write(ex.Message); }
                }
            }

            return rate;
        }

        /// <summary>
        /// Vlozi hodnoceni fotky
        /// </summary>
        /// <param name="photo_id">id fotky</param>
        /// <param name="user_id">id uzivatele</param>
        /// <param name="rate">hodnoceni</param>
        public void InsertRating(int photo_id, int user_id, int rate)
        {
            using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
            {
                connection.Open();

                command = new SqlCommand("INSERT INTO Ratings (photo_id, user_id, rate) VALUES (@photo_id, @user_id, @rate);", connection);
                command.Parameters.Add(new SqlParameter("@photo_id", photo_id));
                command.Parameters.Add(new SqlParameter("@user_id", user_id));
                command.Parameters.Add(new SqlParameter("@rate", rate));

                command.ExecuteNonQuery();
            }
        }
    }
}
